﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace Public {
    public class ExcelHelper  : IDisposable{

        private _Application excelAppliction = null;
        public _Workbook workBook = null;
        public _Worksheet workSheet = null;

        private object missing = Missing.Value;

        public ExcelHelper() {
            if (excelAppliction==null) {
                excelAppliction = new Microsoft.Office.Interop.Excel.Application();
            }
        }

        ~ExcelHelper() {
            if (excelAppliction!=null) {
                excelAppliction.Quit();
            }
        }

        #region IDisposable 成员

        public void Dispose() {
            try {
                if (excelAppliction != null) {
                    if (!workBook.Saved) {
                        this.Close(false);
                    }
                    excelAppliction.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppliction);

                    GC.Collect(System.GC.GetGeneration(workSheet));
                    GC.Collect(System.GC.GetGeneration(workBook));
                    GC.Collect(System.GC.GetGeneration(excelAppliction));

                    if (excelAppliction != null) {
                        excelAppliction = null;
                    }
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                }
            } catch (Exception) {

            } finally {
                GC.Collect();
            }
        }

        #endregion

        /// <summary>
        /// 设置当前工作表
        /// </summary>
        public int CurrentWorksheetIndex {
            set {
                if (value<=0 || value>workBook.Worksheets.Count) {
                    throw new ArgumentException("索引超出范围");
                } else {
                    object index = value;
                    workSheet = workBook.Worksheets[index] as _Worksheet;
                }
            }
        }

        /// <summary>
        /// 打开一个Excel工作薄
        /// </summary>
        /// <param name="fileName"></param>
        public void OpenWorkbook(string fileName) {
            workBook = excelAppliction.Workbooks.Open(fileName, missing, missing, missing, missing, missing,
            missing, missing, missing, missing, missing, missing, missing, missing, missing);
            if (workBook.Worksheets.Count>0) {
                int index = 1;
                workSheet = workBook.Worksheets[index] as _Worksheet;
            }
        
        }


        /// <summary>
        /// 添加一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public Worksheet AddSheet(string sheetName) {
            Worksheet worksheet = (Worksheet)workBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            worksheet.Name = sheetName;
            return worksheet;
        }

        /// <summary>
        /// 删除一个Sheet
        /// </summary>
        /// <param name="sheetName"></param>
        public void DeleteSheet(string sheetName) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            excelAppliction.DisplayAlerts = false;
            sheet.Delete();
            excelAppliction.DisplayAlerts = true;
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        public void Save() {
            if (workBook != null) {
                workBook.Save();
            }
        }

        /// <summary>
        /// 关闭文档
        /// </summary>
        /// <param name="isSave"></param>
        public void Close(bool isSave) {
            object obj_Save = isSave;
            workBook.Close(obj_Save, missing, missing);
        }

        /// <summary>
        /// 设置当前工作表中某单元格的值
        /// </summary>
        /// <param name="range"></param>
        /// <param name="value"></param>
        public void SetRangeValue(string range, object value) {
            SetRangeValue(workSheet.Name, range, value);
        }

        /// <summary>
        /// 设置工作表中某单元格的值
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="range"></param>
        /// <param name="value"></param>
        public void SetRangeValue(string sheetName, string range, object value) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            sheet.get_Range(range, missing).Value2 = value;
        }

        /// <summary>
        /// 删除某个Range，右侧左移
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rangeStr"></param>
        public void DeleteRange(string sheetName, string rangeStr) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            sheet.get_Range(rangeStr, missing).Delete(XlDeleteShiftDirection.xlShiftToLeft);
        }

        /// <summary>
        /// 删除某个Range，右侧左移
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rangeStr"></param>
        public void DeleteRange(string rangeStr) {
            DeleteRange(workSheet.Name, rangeStr);
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="range1"></param>
        /// <param name="range2"></param>
        public void Merge(string sheetName, string range1, string range2) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            Range range = workSheet.get_Range(range1, range2);
            range.Merge(true);
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="range1"></param>
        /// <param name="range2"></param>
        public void Merge(string range1, string range2) {
            Merge(workSheet.Name, range1, range2);
        }

        /// <summary>
        /// 设置一个单元格的属性
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="range"></param>
        /// <param name="size">大小</param>
        /// <param name="name">字体</param>
        /// <param name="color">颜色</param>
        /// <param name="HorizontalAlignment">对齐方式</param>
        public void SetCellProperty(string sheetName, string rangeStr, double fontSize, string fontName,
            double height, Constants horizontalStyle) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            Range range = sheet.get_Range(rangeStr, missing);
            range.Font.Size = fontSize;
            range.Font.Name = fontName;
            //range.Font.Color = fontColor;
            range.RowHeight = height;
            range.HorizontalAlignment = horizontalStyle;
        }

        /// <summary>
        /// 设置一个单元格的属性
        /// </summary>
        /// <param name="range"></param>
        /// <param name="fontSize"></param>
        /// <param name="fontName"></param>
        /// <param name="fontColor"></param>
        /// <param name="horizontalStyle"></param>
        public void SetCellProperty(string range, double fontSize, string fontName,
            double height, Constants horizontalStyle) {
            SetCellProperty(workSheet.Name, range, fontSize, fontName,
                         height, horizontalStyle);
        }

        /// <summary>
        /// 设定Range的边框格式
        /// </summary>
        /// <param name="rangeStart"></param>
        /// <param name="rangeEnd"></param>
        /// <param name="topStyle"></param>
        /// <param name="rightStyle"></param>
        /// <param name="bottomStyle"></param>
        /// <param name="leftStyle"></param>
        public void SetRangeBorderStyle(string sheetName, string rangeStr, XlLineStyle topStyle,
            XlLineStyle rightStyle, XlLineStyle bottomStyle, XlLineStyle leftStyle) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            Range range = sheet.get_Range(rangeStr, missing);
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = topStyle;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = rightStyle;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = bottomStyle;
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = leftStyle;
        }

        /// <summary>
        /// 设定Range的边框格式
        /// </summary>
        /// <param name="rangeStr"></param>
        /// <param name="topStyle"></param>
        /// <param name="rightStyle"></param>
        /// <param name="bottomStyle"></param>
        /// <param name="leftStyle"></param>
        public void SetRangeBorderStyle(string rangeStr, XlLineStyle topStyle,
            XlLineStyle rightStyle, XlLineStyle bottomStyle, XlLineStyle leftStyle) {
            SetRangeBorderStyle(workSheet.Name, rangeStr, topStyle,
             rightStyle, bottomStyle, leftStyle);
        }

        /// <summary>
        /// 设定Range数字格式
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rangeStr"></param>
        /// <param name="format"></param>
        public void SetRangeNumberFormat(string sheetName, string rangeStr, string format) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            Range range = sheet.get_Range(rangeStr, missing);
            range.NumberFormat = format;//0%
        }

        /// <summary>
        /// 设定Range数字格式
        /// </summary>
        /// <param name="rangeStr"></param>
        /// <param name="format"></param>
        public void SetRangeNumberFormat(string rangeStr, string format) {
            SetRangeNumberFormat(workSheet.Name, rangeStr, format);
        }

        /// <summary>
        /// 将数据表格添加到Excel指定工作表的指定位置
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ws"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void AddTable(System.Data.DataTable dt, int startX, int startY) {
            for (int i = 0; i <= dt.Rows.Count - 1; i++) {
                for (int j = 0; j <= dt.Columns.Count - 1; j++) {

                    workSheet.Cells[i + startX, j + startY] = dt.Rows[i][j];
                }
            }
        }

        /// <summary>
        /// 加入分页符
        /// </summary>
        /// <param name="rangeStr"></param>
        /// <param name="lineNumber"></param>
        public void AddPageBreak(string rangeStr, int lineNumber) {
            Range range = workSheet.get_Range("A" + lineNumber.ToString(), missing);
            workSheet.HPageBreaks.Add(range);
        }

        /// <summary>
        /// 加入分页符
        /// </summary>
        /// <param name="lineNumber"></param>
        public void AddPageBreak(int lineNumber) {
            AddPageBreak(workSheet.Name, lineNumber);
        }

        /// <summary>
        /// 从当前工作表数据区域复制数据到另一个区域
        /// </summary>
        /// <param name="sheetFromRange"></param>
        /// <param name="sheetToRange"></param>
        public void CopyRange2Range(string sheetFromRange, string sheetToRange) {
            CopyRange2Range(workSheet.Name, workSheet.Name, sheetFromRange, sheetToRange);
        }

        /// <summary>
        /// 从一个表的某个数据区域复制数据到另一个表的某个区域
        /// </summary>
        /// <param name="sheetFromName"></param>
        /// <param name="sheetToName"></param>
        /// <param name="sheetFromRange"></param>
        /// <param name="sheetToRange"></param>
        public void CopyRange2Range(string sheetFromName, string sheetToName, string sheetFromRange, string sheetToRange) {
            Worksheet sheetFrom = workBook.Worksheets[sheetFromName] as Worksheet;
            Worksheet sheetTo = workBook.Worksheets[sheetToName] as Worksheet;

            sheetFrom.get_Range(sheetFromRange, missing).Copy(sheetTo.get_Range(sheetToRange, missing));
        }

        /// <summary>
        /// 移动Range到另一个Range
        /// </summary>
        /// <param name="FromRange"></param>
        /// <param name="ToRange"></param>
        public void MoveRange2Range(string FromRange, string ToRange) {
            MoveRange2Range(workSheet.Name, FromRange, ToRange);
        }

        /// <summary>
        /// 移动Range到另一个Range
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="FromRange"></param>
        /// <param name="ToRange"></param>
        public void MoveRange2Range(string sheetName, string FromRange, string ToRange) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            sheet.get_Range(FromRange, missing).Cut(sheet.get_Range(ToRange, missing));
        }

        /// <summary>
        /// 重设当前工作目录的打印区域
        /// </summary>
        public void ResetPrintArea() {
            SetPrintArea(workSheet.Name, "");
        }

        /// <summary>
        /// 重设工作目录的打印区域
        /// </summary>
        public void ResetPrintArea(string sheetName) {
            SetPrintArea(sheetName, "");
        }

        /// <summary>
        /// 设定工作目录的打印区域
        /// </summary>
        /// <param name="area"></param>
        public void SetPrintArea(string area) {
            SetPrintArea(workSheet.Name, area);
        }

        /// <summary>
        /// 设定工作目录的打印区域
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="area"></param>
        public void SetPrintArea(string sheetName, string area) {
            Worksheet sheet = workBook.Worksheets[sheetName] as Worksheet;
            sheet.PageSetup.PrintArea = area;
        }

        /// <summary>
        /// 将当前工作表中的表格数据复制到剪切板
        /// </summary>
        public void Copy() {
            if (workSheet != null) {
                try {
                    workSheet.UsedRange.Select();
                } catch { }
                workSheet.UsedRange.Copy(missing);
            }
        }

        public void ImportExcelFrmAdo(string destFileName, string sheetName, System.Data.DataTable dt) {
            System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                    destFileName + ";Extended Properties=Excel 8.0;");
            objConn.Open();
            System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand();
            objCmd.Connection = objConn;
            foreach (System.Data.DataRow row in dt.Rows) {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.AppendFormat("INSERT INTO [{0}$] (", sheetName);
                for (int i = 0; i < dt.Columns.Count; i++) {
                    if (i < dt.Columns.Count - 1)
                        stringBuilder.Append(dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append(dt.Columns[i].ColumnName + ") VALUES (");
                }
                for (int i = 0; i < dt.Columns.Count; i++) {
                    if (i < dt.Columns.Count - 1)
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ",");
                    else
                        stringBuilder.Append("@" + dt.Columns[i].ColumnName + ");");
                }

                objCmd.Parameters.Clear();
                for (int i = 0; i < dt.Columns.Count; i++) {
                    objCmd.Parameters.Add(dt.Columns[i].ColumnName, row[i]);
                }

                objCmd.CommandText = stringBuilder.ToString();
                objCmd.ExecuteNonQuery();

            }
        
        }


    }
}
